﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;

using Bpms.Data;
using Bpms.Entity;
using Bpms.Log;
using Bpms.Common;

namespace Bpms.Data.SqlServer
{
    public partial class DataProvider : IDataProvider
    {
        #region Private

        // 当前文件对应的逻辑类名，用于写日志
        string organizationClassName = "Bpms.Data.SqlServer.Organization";

        /// <summary>
        /// 绑定数据到组织实体
        /// </summary>
        /// <param name="idr">idatareader</param>
        /// <returns>IList<OrganizationInfo>对象</returns>
        IList<OrganizationInfo> GetDataBindedOrganization(IDataReader idr)
        {
            IList<OrganizationInfo> organizationList = new List<OrganizationInfo>();

            while (idr.Read())
            {
                OrganizationInfo organization = new OrganizationInfo();

                organization.OrganizationID = (int)idr["organization_id"];     // 标识ID（部门ID、团队ID）
                organization.ParentID = (int)idr["parent_id"];                 // 上级组织ID
                organization.Name = idr["name"].ToString();                    // 组织名称（部门名称、组织名称）
                organization.Type = (OrganizationType)int.Parse(idr["type"].ToString());    // 组织类型（0：部门，1：团队）
                organization.Remark = idr["remark"].ToString();                // 备注
                organization.IsDelete = Convert.ToBoolean(idr["is_delete"]);   // 是否删除（0：否，1：是）

                organizationList.Add(organization);
            }

            return organizationList;
        }

        /// <summary>
        /// 根据sql语句返回组织信息
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>组织对象</returns>
        OrganizationInfo GetOrganizationBySQL(string sql)
        {
            IList<OrganizationInfo> organizationList = GetOrganizationListBySQL(sql);

            if (organizationList.Count > 0)
            {
                return organizationList[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 根据sql语句返回组织列表
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns> IList<OrganizationInfo>对象</returns>
        IList<OrganizationInfo> GetOrganizationListBySQL(string sql)
        {
            IList<OrganizationInfo> organizationList = new List<OrganizationInfo>();

            using (IDataReader idr = DbHelper.ExecuteReader(CommandType.Text, sql) as IDataReader)
            {
                try
                {
                    organizationList = GetDataBindedOrganization(idr);
                }
                catch (Exception e)
                {
                    log.WriteLog(this.organizationClassName + "：获取绑定的组织信息时出现异常", e);
                }
                finally
                {
                    if (idr != null)
                    {
                        idr.Close();
                    }
                }
            }

            return organizationList;
        }

        #endregion

        /// <summary>
        /// 添加组织（部门、团队）
        /// </summary>
        /// <param name="organization">组织对象（部门、团队）</param>
        /// <returns>添加是否成功（true：成功，false：失败）</returns>
        public bool InsertOrganization(OrganizationInfo organization)
        {
            bool isExecSuccessed = false;

            try
            {
                DbParameter[] parms = 
                {
                    DbHelper.MakeInParam("@organization_id", (DbType)SqlDbType.Int, 4, organization.OrganizationID),        // 标识ID（部门ID、团队ID）
                    DbHelper.MakeInParam("@parent_id", (DbType)SqlDbType.VarChar, 4, organization.ParentID),                // 上级组织ID
                    DbHelper.MakeInParam("@name", (DbType)SqlDbType.NVarChar, 30, organization.Name),                       // 组织名称（部门名称、团队名称）
                    DbHelper.MakeInParam("@type", (DbType)SqlDbType.Int, 4, organization.Type),                             // 组织类型（0：部门，1：团队）
                    DbHelper.MakeInParam("@remark", (DbType)SqlDbType.NVarChar, 200, organization.Remark),                  // 备注
                    DbHelper.MakeInParam("@is_delete", (DbType)SqlDbType.TinyInt, 1, organization.IsDelete),                // 是否删除（0：否，1：是）
                };

                if (DbHelper.ExecuteNonQuery(CommandType.StoredProcedure, "bpms_add_organization", parms) > 0)
                {
                    isExecSuccessed = true;
                }
            }
            catch (Exception e)
            {
                log.WriteLog(this.organizationClassName + "：添加组织信息时出现异常", e);
            }

            return isExecSuccessed;
        }

        /// <summary>
        /// 修改组织（部门、团队）
        /// </summary>
        /// <param name="organization">组织对象（部门、团队）</param>
        /// <returns>修改是否成功（true：成功，false：失败）</returns>
        public bool UpdateOrganization(OrganizationInfo organization)
        {
            bool isExecSuccessed = false;

            try
            {
                DbParameter[] parms = 
                {
                    DbHelper.MakeInParam("@organization_id", (DbType)SqlDbType.Int, 4, organization.OrganizationID),        // 标识ID（部门ID、团队ID）
                    DbHelper.MakeInParam("@parent_id", (DbType)SqlDbType.VarChar, 4, organization.ParentID),                // 上级组织ID
                    DbHelper.MakeInParam("@name", (DbType)SqlDbType.NVarChar, 30, organization.Name),                       // 组织名称（部门名称、团队名称）
                    DbHelper.MakeInParam("@type", (DbType)SqlDbType.Int, 4, organization.Type),                             // 组织类型（0：部门，1：团队）
                    DbHelper.MakeInParam("@remark", (DbType)SqlDbType.NVarChar, 200, organization.Remark),                  // 备注
                };

                if (DbHelper.ExecuteNonQuery(CommandType.StoredProcedure, "bpms_update_organization", parms) > 0)
                {
                    isExecSuccessed = true;
                }
            }
            catch (Exception e)
            {
                log.WriteLog(this.organizationClassName + "：修改组织信息时出现异常", e);
            }

            return isExecSuccessed;
        }

        /// <summary>
        /// 删除组织（部门、团队）
        /// </summary>
        /// <param name="organizationId">组织对象（部门、团队）ID</param>
        /// <returns>删除是否成功（true：成功，false：失败）</returns>
        public bool DeleteOrganization(int organizationId)
        {
            bool isExecSuccessed = false;

            try
            {
                DbParameter[] parms = 
                {
                    DbHelper.MakeInParam("@organization_id", (DbType)SqlDbType.Int, 4, organizationId)        // 标识ID（部门ID、团队ID）
                };

                if (DbHelper.ExecuteNonQuery(CommandType.StoredProcedure, "bpms_delete_organization", parms) > 0)
                {
                    isExecSuccessed = true;
                }
            }
            catch (Exception e)
            {
                log.WriteLog(this.organizationClassName + "：删除组织信息时出现异常", e);
            }

            return isExecSuccessed;
        }

        /// <summary>
        /// 判断某组织名称是否存在
        /// </summary>
        /// <param name="organization">组织对象（部门、团队）</param>
        /// <param name="operateType">判断的类型（添加或修改）</param>
        /// <returns>组织名称是否存在（true：已存在，false：不存在）</returns>
        public bool OrganizationExists(OrganizationInfo organization, OperateType type)
        {
            StringBuilder sbSql = new StringBuilder("SELECT 1 FROM organization_id WHERE ");
            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@organization_id", (DbType)SqlDbType.Int, 4, organization.OrganizationID), 
                DbHelper.MakeInParam("@name", (DbType)SqlDbType.NVarChar, 30, organization.Name),
                DbHelper.MakeInParam("@organizationType", (DbType)SqlDbType.TinyInt, 1, organization.Type)
            };

            switch (type)
            {
                case OperateType.Insert:
                    sbSql.AppendFormat(" [name] = @name AND [type] = @organizationType ");
                    break;
                case OperateType.Update:
                    sbSql.AppendFormat(" [name] = @name AND [type] = @organizationType AND [organization_id] <> @organization_id ");
                    break;
                default:
                    log.WriteLog(this.organizationClassName + "：检测组织名称是否存在，参数错误，未指定检测类型，将按照已经存在处理，防止添加新的同名组织名称");
                    sbSql.AppendFormat(" 1 = 2 ");
                    break;
            }
            try
            {
                if (DbHelper.ExecuteScalar(CommandType.Text, sbSql.ToString(), parms) != null)
                {
                    return true;
                }
            }
            catch (Exception e)
            {
                log.WriteLog(this.organizationClassName + "：判断组织名称是否存在时产生异常", e);
            }

            return false;
        }

        /// <summary>
        /// 得到组织（部门、团队）对象
        /// </summary>
        /// <param name="organizationId">组织（部门、团队）ID</param>
        /// <returns>组织对象</returns>
        public OrganizationInfo GetOrganization(int organizationId)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(" SELECT organization_id, parent_id, [name], type, remark FROM bp_organization ");
            sbSql.Append(" WHERE is_delete = 0 AND organization_id = ").Append(organizationId);

            return GetOrganizationBySQL(sbSql.ToString());
        }

        /// <summary>
        /// 得到组织（部门、团队）对象列表
        /// </summary>
        /// <param name="pageId">当前页ID</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="condition">提取的条件</param>
        /// <param name="organizationType">提取的组织类型（部门或团队）</param>
        /// <returns>组织对象列表</returns>
        public IList<OrganizationInfo> GetOrganizations(int pageId, int pageSize, string condition, OrganizationType organizationType)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(" SELECT TOP {0} organization_id, parent_id, [name], type, remark, is_delete ");
            sbSql.Append(" FROM bp_organization ");
            sbSql.Append(" WHERE {2} AND is_delete = 0 organization_id NOT IN ");
            sbSql.Append(" ( ");
            sbSql.Append("      SELECT TOP {1} organization_id FROM bp_organization ");
            sbSql.Append("       WHERE {2} AND is_delete = 0 ");
            sbSql.Append("       ORDER BY organization_id DESC ");
            sbSql.Append(" ) ");
            sbSql.Append(" ORDER BY organization_id DESC ");

            string sql = sbSql.ToString();
            sbSql.Remove(0, sbSql.Length);
            sbSql.AppendFormat(sql, pageSize, pageSize * (pageId - 1), condition);

            return GetOrganizationListBySQL(sbSql.ToString());
        }

        /// <summary>
        /// 得到组织（部门、团队）对象列表
        /// </summary>
        /// <param name="condition">提取的条件</param>
        /// <returns>组织对象列表</returns>
        public IList<OrganizationInfo> GetOrganizations(string condition)
        {
            StringBuilder sbSql = new StringBuilder();

            sbSql.Append(" SELECT organization_id, parent_id, [name], type, remark, is_delete FROM bp_organization ");
            sbSql.Append(" WHERE is_delete = 0 ");
            if (condition.Trim().Length != 0)
            {
                sbSql.Append(" AND ").Append(condition);
            }
            else
            {
                sbSql.Append(" AND 1=1 ");
            }

            sbSql.Append(" ORDER BY organization_id DESC ");
            
            return GetOrganizationListBySQL(sbSql.ToString());
        }

        /// <summary>
        /// 为团队分配联系人
        /// </summary>
        /// <param name="organizationId">团队ID</param>
        /// <param name="contactIdList">分配的联系人列表</param>
        /// <returns>操作是否成功（true：成功，false：失败）</returns>
        public bool DistributeContact(int organizationId, string[] contactIdList)
        {
            bool isExecSucceed = false;

            foreach (string contactId in contactIdList)
            {
                if (!Validity.IsNumber(contactId))
                {
                    return false;
                }
            }

            using (DbConnection dbConn = DbHelper.Connection)
            {
                dbConn.Open();
                using (DbTransaction dbTrans = dbConn.BeginTransaction())
                {
                    try
                    {
                        // 删除原来分配给该团队的联系人
                        string sql = " DELETE FROM bp_organization_contact WHERE organization_id = @organization_id ";
                        DbParameter[] parms = 
                        { 
                            DbHelper.MakeInParam("@organization_id", (DbType)SqlDbType.Int, 4, organizationId) 
                        };
                        DbHelper.ExecuteNonQuery(dbTrans, CommandType.Text, sql, parms);

                        // 为团队分配客户
                        sql = " INSERT INTO bp_organization_contact(organization_id, contact_id) VALUES (@organization_id, @contact_id) ";

                        foreach (string contactId in contactIdList)
                        {
                            parms = new DbParameter[]
                            {
                                DbHelper.MakeInParam("@organization_id", (DbType)SqlDbType.Int, 4, organizationId),
                                DbHelper.MakeInParam("@contact_id", (DbType)SqlDbType.Int, 4, int.Parse(contactId))
                            };
                            DbHelper.ExecuteNonQuery(dbTrans, CommandType.Text, sql, parms);
                        }

                        dbTrans.Commit();
                        isExecSucceed = true;
                    }
                    catch (Exception e)
                    {
                        dbTrans.Rollback();

                        log.WriteLog(this.organizationClassName + "：为团队分配联系人时产生异常", e);
                    }
                }
            }

            return isExecSucceed;
        }

        /// <summary>
        /// 得到为该团队分配的联系人列表
        /// </summary>
        /// <param name="organizationId">团队ID</param>
        /// <returns>该团队分配的联系人列表</returns>
        public IList<ContactInfo> GetOrganizationContacts(int organizationId)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(" SELECT [contact_id] FROM bp_organization_contact WHERE [organization_id] = @organization_id ");
            sbSql.Append(" ORDER BY [contact_id] DESC ");

            DbParameter[] parms =
            { 
                DbHelper.MakeInParam("@organization_id", (DbType)SqlDbType.Int, 4, organizationId)
            };
            using (DbDataReader reader = DbHelper.ExecuteReader(CommandType.Text, sbSql.ToString(), parms))
            {
                if (null == reader)
                {
                    return null;
                }
                try
                {
                    IList<ContactInfo> contacts = new List<ContactInfo>();
                    ContactInfo contact = null;
                    while (reader.Read())
                    {
                        contact = new ContactInfo();
                        contact.ContactID = (int)reader["contact_id"];
                        contacts.Add(contact);
                    }
                    return contacts;
                }
                catch (Exception e)
                {
                    log.WriteLog(this.organizationClassName + "：得到为团队分配的联系人时产生异常", e);
                    return null;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                        reader.Dispose();
                    }
                }
            }
        }

        /// <summary>
        /// 判断是否为团队分配了某一联系人
        /// </summary>
        /// <param name="contacts">为该团队分配的联系人集合</param>
        /// <param name="contactId">某一联系人ID</param>
        /// <returns>是否分配（true：已分配，false：未分配）</returns>
        public bool IsContainsContact(IList<ContactInfo> contacts, int contactId)
        {
            bool isContains = false;
            foreach (ContactInfo contact in contacts)
            {
                if (contact.ContactID == contactId)
                {
                    isContains = true;
                    break;
                }
            }

            return isContains;
        }

        /// <summary>
        /// 得到组织（部门、团队）名称
        /// </summary>
        /// <param name="organizationId">组织（部门、团队）ID</param>
        /// <returns>组织名称</returns>
        public string GetOrganizationName(int organizationId)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(" SELECT [name] FROM bp_organization WHERE organization_id = @organization_id ");

            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@organization_id", (DbType)SqlDbType.Int, 4, organizationId) 
            };
            object organizationName = null;
            try
            {
                organizationName = DbHelper.ExecuteScalar(CommandType.Text, sbSql.ToString(), parms) ?? "";
            }
            catch (Exception e)
            {
                log.WriteLog(this.organizationClassName + "：根据组织的ID，得到组织名称时出现异常", e);
            }

            return organizationName.ToString();
        }
    }
}
